package com.x.ant.dao;

import com.x.ant.model.Dept;
import com.x.ant.model.DeptUser;
import com.x.framework.dao.BaseDao;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Repository
public class DeptDao extends BaseDao {

    private final static String selectDeptListSql = "SELECT T1.*, T2.PROVINCE_NAME, T3.CITY_NAME, T4.DISTRICT_NAME " +
            "FROM ANT_DEPT T1, BASE_PROVINCE T2, BASE_CITY T3, BASE_DISTRICT T4 " +
            "WHERE T1.PROVINCE_ID = T2.PROVINCE_ID AND T1.CITY_ID = T3.CITY_ID AND T1.DISTRICT_ID = T4.DISTRICT_ID ";
    public List<Dept> selectDeptList(Dept dept) throws Exception{
        StringBuilder sb = new StringBuilder(selectDeptListSql);
        List params = new ArrayList();
        if(this.isString(dept.getDeptId())){
            sb.append("AND T1.DEPT_ID = ? ");
            params.add(dept.getDeptId());
        }
        if(this.isString(dept.getDeptParentId())){
            sb.append("AND T1.DEPT_PARENT_ID = ? ");
            params.add(dept.getDeptParentId());
        }
        if(this.isString(dept.getDeptCode())){
            sb.append("AND T1.DEPT_CODE LIKE ? ");
            params.add(dept.getDeptCode() + "%");
        }
        if(dept.getDeptType() != null) {
            sb.append("AND T1.DEPT_TYPE = ? ");
            params.add(dept.getDeptType());
        }
        sb.append("ORDER BY DEPT_CODE");
        return this.queryForList(sb.toString(), Dept.class, params);
    }

    private final static String selectDeptUserListSql = "SELECT T1.* " +
            "FROM ANT_DEPT T1, ANT_DEPT_USER T2 " +
            "WHERE T1.DEPT_ID = T2.DEPT_ID AND T2.USER_ID = ? ";
    public List<Dept> selectDeptUserList(DeptUser deptUser) throws Exception{
        StringBuilder sb = new StringBuilder(selectDeptUserListSql);
        Object[] params = {deptUser.getUserId()};
        return this.queryForList(sb.toString(), Dept.class, params);
    }

    private final static String selectMaxCodeSql = "SELECT MAX(DEPT_CODE) AS MAX_CODE FROM ANT_DEPT WHERE DEPT_PARENT_ID = ? ";
    public String selectMaxCode(String parentId) throws Exception {
        Object[] params = {parentId};
        RowMapper<String> mapper = new RowMapper<String>() {
            public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                return rs.getString("MAX_CODE");
            }
        };
        Object maxCode = this.queryForObject(selectMaxCodeSql, mapper, params);
        if (maxCode != null) {
            return maxCode.toString();
        } else  {
            return null;
        }
    }


}
